Spreadsheet applications are widely used in the business- and education sector. One reason for their ubiquitous use is that they provide end-users not skilled in any programming language with means to specify calculations of various levels of complexity.
A formula specified within a cell can be of arbitrary complexity. In order to specify highly complex calculations in a spreadsheet application, commonly a multitude of formulas contained in a multitude of cells referencing each other are specified by a user.
It is a common requirement to execute complex calculations within a spreadsheet application on multiple different sets of input parameters and to compare the results generated on said different input data sets in order to evaluate the impact of the input data values on the final result. A scientist may have developed a complex formula representing a scientific theory and may want to evaluate the impact of different input data values on the result generated by his model. An accountant may want to know the impact of a reduction of costs in one item of expense on the final result calculated. A family may want to document and monitor the monthly income and expenses of each family member with the help of formulas specified in a spreadsheet document. In any of those cases it is required that the complex calculation, once having been specified, can be reused on different sets of input parameter values.
A related and common requirement is to execute nested, e.g. recursive, functions. A typical example of executing a nested function is the calculation of the surrender value of a life insurance over multiple years, given a particular amount of annual savings and a particular interest rate as input parameters. The calculation may soon get even more complex, if one of the input parameters, e.g. the annual savings and/or the annual interest rate is not constant over time, as it is often the case in practice.
Despite the ubiquity of the usage of spreadsheet applications such as EXCEL for calculating results from different input parameter values, existing spreadsheet applications do not provide means for displaying multiple result values generated by the same spreadsheet-function on different sets of input parameter values at the same time. The means provided by existing spreadsheet applications for displaying and evaluating the impact of a particular input data value on the final result are not intuitive, difficult to maintain and/or not applicable for users not familiar with a programming language (i.e. the broad majority of all users of spreadsheet applications).
Reusing Complex Calculations—State of the Art:
The most widely used spreadsheet application program, Microsoft EXCEL, provides users with the option to specify complex calculations by specifying a multitude of formula cells referencing each other. Basically, EXCEL provides two options for reusing a complex calculation multiple times on different input data sets after said calculation has been specified: the ‘code-duplication’ option and the ‘scenario manager’ option.
A complex calculation is usually specified in EXCEL via a multitude of cells referencing each other and comprising formulas and/or data values. If a user chooses the ‘code duplication’ option to apply such a complex calculation on two or more different sets of input parameter values, he or she has to copy the totality of cells specifying the complex calculation, paste said copied cells into another section of the same worksheet or into another worksheet, and edit the data values used as input by the duplicated cells.
A further means provided by EXCEL for specifying and reusing complex spreadsheet-functions on two or more different input data sets is the ‘scenario-manager’: a scenario is a set of input parameter values which is used to calculate an output parameter value. The ‘scenario manager’ manages different sets of input parameter values, each set corresponding to and representing a separate scenario. With the help of the scenario manager, the user can create different sets of input parameter values which are all assigned to one particular spreadsheet-function, e.g. a summarization or a complex user-defined mathematical function. Each scenario comprises a result value having been calculated on the input parameter set of said scenario. By switching between different scenarios, a user can evaluate the impact of different sets of input parameter values on the calculation result. However, as the two or more specified scenarios are displayed by the scenario manager in mutual exclusion, the user is not able to see and compare the two or more results at the same time. In addition, the results generated by the scenario-manager cannot be reused by other spreadsheet-functions.
Some spreadsheet applications, such as EXCEL, provide the user with the possibility to specify functions by means of a programming language such as VBA (Visual Basic for Applications). A person with programming skills may also use a programming language to create functions which can be applied multiple times on different sets of input parameter values. In practice, however, the overwhelming majority of EXCEL users does not have programming skills and is forced to rely solely on the ‘copy-paste’ option and the ‘scenario manager’.